﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DataBase;
using PiaExcel;
using EXCEL = Microsoft.Office.Interop.Excel;
namespace DataBaseApp
{
    public static class CaseList
    {

#if tru
        #region 案例1,TXT输出
        public static void CsvWriteLine()
        {
            using (OracleManager ob = new OracleManager(DataBaseType.DBBG))
            {

                string path = @"z:\新建文本文档.txt";
                CsvWriter wt = new CsvWriter(path);

                IEnumerable<Batch> item = ob.ExecuteProcedursToBatch("pkg_report_nissan_sales.proc_sub_terminal_sales_age", 10000, "201704", "t");
                {
                    wt.WriteLine(true, item);
                }
            }
        }
        #endregion
#endif


#if tru
        #region 案例2,分批次输出(可用于生成不同文件
        public static void ExcelWritLineToBatch()
        {
            //)
            using (OracleManager ob = new OracleManager(DataBaseType.DBBG))
            {
                PiaWorkbook wb = new PiaWorkbook(@"Z:\Test.xlsx");
                PiaWorkSheet ws = wb.WorkSheet("Sheet1");
                int outPutRow = 1;
                bool isHeader = true;

                //item为每次返回指定行数的批次数据
                foreach (var item in ob.ExecuteProcedursToBatch("pkg_report_nissan_sales.proc_sub_terminal_sales_age", 10000, "201704", "t"))
                {
                    ws.WriteToExcel(isHeader, outPutRow, 1, item);
                    outPutRow = ws.Range("A1").XlDownRow + 1;
                    isHeader = false;

                }
            }
        }
        #endregion
#endif



#if tru
        #region 案例3,全量输出
        public static void ExcelWritLineToRow()
        {

            using (DataBase.OracleManager ob = new OracleManager(DataBaseType.DBBG))
            {
                bool tableHeaderView = true;
                int outPutRow = 1;
                PiaWorkbook wb = new PiaWorkbook(@"Z:\Test.xlsx");
                PiaWorkSheet ws = wb.WorkSheet("Sheet1");

                //全量输出
                ws.WriteToExcel(tableHeaderView, outPutRow, 1, ob.ExecuteProcedursToRow("pkg_report_nissan_sales.proc_sub_terminal_sales_age", "201704", "t"));
            }
        }
        #endregion
#endif


#if tru
        #region Home-案例4,EXCEL输出
        public static void Test()
        {

            using (DataBase.OracleManager ob = new OracleManager(DataBaseType.Oracle))
            {
                int c = 0;
                bool isHeader = true;
                int outPutRow = 1;
                PiaWorkbook wb = new PiaWorkbook(@"D:\Test.xlsx");
                PiaWorkSheet mws = wb.WorkSheet("Sheet1");
                PiaRange mRng = mws.Range("A1", "A3");

                foreach (var item in ob.ExecuteProcedursToBatch("car_info_api.query_submodel_to_version", 15, "106294", "t"))
                {
                    c++;

                    if (c != 1)
                    {
                        isHeader = false;

                    }
                    PiaRange rng = wb.WorkSheet("Sheet1").Range("A1");
                    mws.WriteToExcel(isHeader, outPutRow, 1, item);
                    outPutRow = rng.XlDownRow + 1;
                }
            }
        }
        #endregion
#endif



#if tru
        #region Home-案例5,CSV输出
        public static void Test2()
        {

            using (DataBase.OracleManager ob = new OracleManager(DataBaseType.Oracle))
            {
                string path = @"D:\Test.txt";
                CsvWriter wt = new CsvWriter(path);
                wt.WriteLine(true, ob.ExecuteTable("SELECT * FROM v_submodel_to_version"));
            }
        }
        #endregion
        
#endif



        #region 输出EXCEL指定行数据

        public static void Test_GetRowsValue()
        {
            PiaWorkbook wb = new PiaWorkbook(@"Z:\子车型列表-15标准-201705112.xlsx");
            PiaWorkSheet ws = wb.WorkSheet("Sheet1");
            PiaRange rng = ws.Range(ws.WorkSheet.UsedRange.Address);
            PiaRange rng2 = wb.WorkSheet("Sheet2").Range("A1");



            foreach (var item in rng.RowsValue())
            {

                string t = rng2.Range.Address;
                rng2.Range = rng2.Range.Offset[1];
                t = rng2.Range.Address;
                EXCEL.Range outputRange = rng2.Range.get_Resize(1, rng.ColumnsCount);
                outputRange.Value = item.Row;

            }


        }
        #endregion

    }
}


